package cn.com.text.Util;

import android.os.Handler;
import android.os.Message;
import android.util.Log;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import cn.com.text.Dao.News;

import static java.lang.Thread.sleep;

/**
 * Created by 86176 on 2021/12/22.
 */

public class MySQLConnections {
    public static int number = 0;
    private String driver = "";
    private String dbURL = "";
    private String user = "";
    private String password = "";
    private static MySQLConnections connection = null;
    private static Connection conn = null;
    private static PreparedStatement stmt = null;
    private static List<News>newsList = new ArrayList<News>();
    private MySQLConnections() throws Exception {
        driver = "com.mysql.jdbc.Driver";
        dbURL = "jdbc:mysql://182.92.153.231:3306/blog";
        user = "lkk";
        password = "123456";
        System.out.println("dbURL:" + dbURL);
    }
    public static Connection getConnection(String dbName) {
        Connection conn = null;
        if (connection == null) {
            try {
                connection = new MySQLConnections();
            } catch (Exception e) {
                e.printStackTrace();
                return null;
            }
        }
        try {
            Class.forName(connection.driver);
            conn = DriverManager.getConnection(connection.dbURL,
                    connection.user, connection.password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static int insertIntoData(final int id, final String author,final String title,final String content,String image) throws SQLException {//增加数据
        Connection  conn = null;
        conn = getConnection("blog");//填写需要连接的数据库的名称，我是用的是person
        //使用DriverManager获取数据库连接
        Statement stmt = conn.createStatement();
        //使用Connection来创建一个Statment对象
        String sql = "insert INTO Blog (id,author,title,content,image)VALUES('"+id+"','"+author+"','"+title+"','"+content+"','"+image+"')";
        return stmt.executeUpdate(sql);//返回的同时执行sql语句，返回受影响的条目数量，一般不作处理
    }
    public static List<News> querycol() throws SQLException {//读取某一行
        //加载数据库驱动
        final Handler handler = new Handler(new Handler.Callback() {
            @Override
            public boolean handleMessage(Message message) {
                //下一步执行的代码
                return false;
            }
        });
        new Thread(new Runnable() {
            @Override
            public void run() {
                Message msg = new Message();//message用于给handler传递参数
                try {
                    //调用连接数据库方法的代码
                    conn = MySQLConnections.getConnection("Blog");
                } catch (Exception e) {
                    e.printStackTrace();
                    Log.d("TAG", " 数据操作异常");
                }handler.sendMessage(msg);
                try {
                    String sql = "select * from Blog";
                    if(conn != null){
                        stmt = conn.prepareStatement(sql);
                        conn.setAutoCommit(false);
                        ResultSet rs =stmt.executeQuery();//用rs接收sql语句返回的查询结果
                        //执行查询语句并且保存结果
                        while (rs.next()){
                            News news = new News();
                            news.setId(Integer.parseInt(rs.getString("id")));
                            news.setAuthor(rs.getString("author"));
                            news.setTitle(rs.getString("title"));
                            news.setContent(rs.getString("content"));
                            news.setImage(rs.getString("image"));
                            newsList.add(news);
                        }
                        rs.close();
                    }
                    sleep(2000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }).start();
        return newsList;
    }
    public static int insertIntoUser(String name, String password, String image) throws SQLException {
        Connection  conn = null;
        conn = getConnection("blog");//填写需要连接的数据库的名称，我是用的是person
        //使用DriverManager获取数据库连接
        Statement stmt = conn.createStatement();
        //使用Connection来创建一个Statment对象
        String sql = "insert INTO User (name,password,image)VALUES('"+name+"','"+password+"','"+image+"')";
        return stmt.executeUpdate(sql);
    }
}
